package in

import (
	"database/sql"
	"fmt"
	"log"
	"regexp"
	"strings"

	_ "github.com/go-sql-driver/mysql"
	_ "github.com/mattn/go-sqlite3"
)

type SqlBottle struct {
	Bottle
	DataName string
}

func NewSqlBottle(initInfo string) (bottle *SqlBottle, err error) {
	if !strings.Contains(initInfo, "sql") {
		return
	}
	bottle = new(SqlBottle)
	bottle.Data = initInfo
	tps := strings.SplitN(initInfo, "://", 2)
	ColorL(tps[0], tps[1])

	db, err := sql.Open(tps[0], tps[1])

	bottle.Engine = db
	bottle.IsDB = true
	bottle.Tp = tps[0]
	if tps[0] == "mysql" {
		if tmp := strings.Split(tps[1], "/"); len(tmp) > 0 {
			bottle.DataName = strings.SplitN(tmp[len(tmp)-1], "?", 2)[0]
		}
	}

	return
}

type SqlOper struct {
	Oper
}

func (sql *SqlBottle) GetName() string {
	return sql.Data
}
func (mdb *SqlBottle) GetTp() string {
	return mdb.Tp
}

func (dbbottle *SqlBottle) Desc(oper *Oper) (keys S) {
	tbname := oper.Name
	database := dbbottle.DataName

	// if tmp := strings.Split(dbbottle.Data, "/"); len(tmp)>
	if dbbottle.Tp == "sqlite3" {
		if tbname == "" {
			for _, kv := range dbbottle.Query(OperWith("sqlite_master", "name")) {
				keys = append(keys, kv["name"].(string))
			}
			return
		}
		res := dbbottle.Query(OperWith("sqlite_master", "sql").Grep(G{
			"name": tbname,
		}))

		needString := res[0]["sql"].(string)

		itemsRe := regexp.MustCompile(`\(([\w\W]+)\)`)

		reString := itemsRe.FindString(needString)
		reString = reString[1 : len(reString)-1]

		// log.Println("debug", reString)
		for _, v := range strings.Split(reString, ",") {
			// log.Println("debug", reString, v)
			keyName := strings.Fields(v)[0]
			if strings.HasPrefix(keyName, "`") {
				keyName = keyName[1 : len(keyName)-1]
			}
			keys = append(keys, keyName)
		}
	} else if dbbottle.Tp == "mysql" {
		if tbname == "" {

			for _, v := range dbbottle.Query(OperWith("").Set(G{"raw": "SHOW tables "})) {
				keys = append(keys, string(v["Tables_in_"+database].([]byte)))
			}
			return
		}
		for _, v := range dbbottle.Query(OperWith("").Set(G{"raw": "DESCRIBE " + tbname})) {
			keys = append(keys, string(v["Field"].([]byte)))
		}
	}
	return
}

func (dbbottle *SqlBottle) Query(oper *Oper) Datas {
	defer func() {
		if len(GlobalOperChannel) > 0 {
			<-GlobalOperChannel
		}
	}()
	GlobalOperChannel <- 0

	tableName := oper.Name
	filter := oper.Filter
	items := oper.Items
	length := oper.Len
	page := oper.Page
	db := dbbottle.Engine.(*sql.DB)
	if length == 0 {
		length = 1000
	}
	its := "*"
	if len(items) > 0 {
		its = strings.Join(items, ",")
	}
	sqlReadall := fmt.Sprintf(`SELECT %s FROM %s `, its, tableName)

	var rows *sql.Rows
	var err error
	sqlTail := fmt.Sprintf("LIMIT %d OFFSET %d", length, page*length)

	if len(filter) > 0 {
		var keys []string
		var values []interface{}

		sqlReadall += "WHERE "
		for filterK, filterV := range filter {
			// switch filterV.(type) {
			// case string:
			// 	keys = append(keys, fmt.Sprintf("%s=? ", filterK))
			// default:
			// 	keys = append(keys, fmt.Sprintf("%s=?", filterK))
			// }
			keys = append(keys, fmt.Sprintf("%s=? ", filterK))
			values = append(values, filterV)
		}
		sqlReadall += strings.Join(keys, " AND ")
		sqlReadall += sqlTail
		ColorS(sqlReadall)
		log.Println(values)
		rows, err = db.Query(sqlReadall+";", values...)

		if err != nil {

			log.Println("filter sql:", sqlReadall)
			panic(err)
		}
	} else {
		if oper.Raw != "" {
			rows, err = db.Query(oper.Raw + ";")
		} else {
			rows, err = db.Query(sqlReadall + sqlTail + ";")
		}
		if err != nil {
			log.Println("sql:", sqlReadall)
			panic(err)
		}
	}
	defer rows.Close()
	var result Datas
	cols, _ := rows.Columns()
	for rows.Next() {
		columns := make([]interface{}, len(cols))
		columnPointers := make([]interface{}, len(cols))
		item := make(G)
		for i := range columns {
			columnPointers[i] = &columns[i]
		}

		// Scan the result into the column pointers...
		if err := rows.Scan(columnPointers...); err != nil {
			log.Println("SQL scan Err:", err)
		}

		for i, colName := range cols {
			val := columnPointers[i].(*interface{})
			// log.Println(val)
			item[colName] = *val
		}

		result = append(result, item)
	}
	// return result
	// log.Println(result)
	if !dbbottle.NoCached {
		SaveInCache(dbbottle.Data+"/"+oper.Name, result)
	}
	if dbbottle.Tp == "mysql" && oper.Raw == "" {
		return result.AutoTrans(dbbottle, tableName)
	}

	return result
}
